-- @owner: @zhanghuan96
-- @date: 2024/11/5
-- @testpoint: GMS_XMLGEN.GETXMLTYPE语法1，参数个数测试
@conn PrimaryDbAmode;
--前置条件
drop extension if exists gms_xmlgen;
create extension gms_xmlgen;
set behavior_compat_options = 'bind_procedure_searchpath';
--step1:建表;expect:成功
drop table if exists t_getxmltype0001;
create table t_getxmltype0001 (
    "integer" integer,
    "float" float,
    "numeric" numeric(20, 6),
    "boolean" boolean,
    "char" char(20),
    "varchar" varchar(20),
    "text" text,
    "blob" blob,
    "raw" raw,
    "date" date,
    "time" time,
    "timestamp" timestamp,
    "json" json,
    "varchar_array" varchar(20)[],
    "money" money,
    "bit" bit(3),
    c_xml xml
);
insert into t_getxmltype0001
values(
        1,
        1.23456,
        1.234567,
        true,
        '"''<>&char test',
        'varchar"''<>&test',
        'text test"''<>&',
        'ff',
        hextoraw('ABCD'),
        '2024-01-02',
        '18:01:02',
        '2024-02-03 19:03:04',
        '{"a" : 1, "b" : 2}',
        array['abc', '"''<>&', '你好'],
         '12.34'::float8::numeric::money,
         B'101',
          '<value>one</value>');
--step2:只有参数1，参数1是上下文id;expect:成功
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_cxt := gms_xmlgen.newcontext('select * from t_getxmltype0001');
xml_type := gms_xmlgen.getxmltype(xml_cxt);
RAISE INFO 'INFO:XML Output: %', xml_type::text;
gms_xmlgen.closecontext(xml_cxt);
END;
/

--step3:2个参数，参数1是上下文id，参数2传入有效值;expect:成功
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_cxt := gms_xmlgen.newcontext('select * from t_getxmltype0001');
xml_type := gms_xmlgen.getxmltype(xml_cxt, 1);
RAISE INFO 'INFO:XML Output: %', xml_type::text;
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step4:清理环境;expect:成功
drop table if exists t_getxmltype0001;
drop extension if exists gms_xmlgen;